package es.udc.fbellas.j2ee.tiendaweb.model.usuario.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import es.udc.fbellas.j2ee.tiendaweb.model.usuario.TO.UsuarioProfileTO;
import es.udc.fbellas.j2ee.util.exceptions.DuplicateInstanceException;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public class SQLUsuarioProfileDAO {

    public void create(Connection connection, UsuarioProfileTO usuarioProfileTO)
        throws DuplicateInstanceException, InternalErrorException {
        
        /* Check if the user already exists. */
        if (exists(connection, usuarioProfileTO.getLogin())) {
            throw new DuplicateInstanceException(usuarioProfileTO.getLogin(),
                UsuarioProfileTO.class.getName());
        }

        PreparedStatement preparedStatement = null;
    
        try {

            /* Create "preparedStatement". */
            String queryString = "INSERT INTO USUARIOPROFILE" +
                " (LOGIN, ENPASSWORD, NOMBRE, APELLIDOS, EMAIL, IDDIR, NUMTARJETA, LANGUAGE, COUNTRY)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(queryString);    
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setString(i++, usuarioProfileTO.getLogin());
            preparedStatement.setString(i++, usuarioProfileTO.getEncryptedPassword());
            preparedStatement.setString(i++, usuarioProfileTO.getNombre());
            preparedStatement.setString(i++, usuarioProfileTO.getApellidos());
            preparedStatement.setString(i++, usuarioProfileTO.getEmail());
            preparedStatement.setInt(i++, usuarioProfileTO.getIdDir());
            preparedStatement.setLong(i++, usuarioProfileTO.getNumTarjeta());
            preparedStatement.setString(i++, usuarioProfileTO.getLanguage());
            preparedStatement.setString(i++, usuarioProfileTO.getCountry());
                        
            /* Execute query. */
            int insertedRows = preparedStatement.executeUpdate();
        
            if (insertedRows == 0) {
                throw new SQLException("Can not add row to table" +
                    " 'USUARIOPROFILE'");
            }

            if (insertedRows > 1) {
                throw new SQLException("Duplicate row for login name = '" + 
                    usuarioProfileTO.getLogin() + "' in table 'USUARIOPROFILE'");
            }
        
        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }    
        
    }

    public boolean exists(Connection connection, String login)
        throws InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            /* Create "preparedStatement". */
            String queryString = "SELECT LOGIN FROM USUARIOPROFILE" +
                " WHERE LOGIN = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setString(i++, login);
            
            /* Execute query. */
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }
        
    }

    public UsuarioProfileTO find(Connection connection, String login)
        throws InstanceNotFoundException, InternalErrorException {
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            /* Create "preparedStatement". */
            String queryString = "SELECT ENPASSWORD, NOMBRE, APELLIDOS," +
                " EMAIL, IDDIR, NUMTARJETA, LANGUAGE, COUNTRY FROM USUARIOPROFILE WHERE" +
                " LOGIN = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setString(i++, login);
            
            /* Execute query. */
            resultSet = preparedStatement.executeQuery();
            
            if (!resultSet.next()) {
                throw new InstanceNotFoundException(login,
                    UsuarioProfileTO.class.getName());
            }

            /* Get results. */
            i = 1;
            String enPassword = resultSet.getString(i++);
            String nombre = resultSet.getString(i++);
            String apellidos = resultSet.getString(i++);
            String email = resultSet.getString(i++);
			int idDir = resultSet.getInt(i++);
			Long numTarjeta = resultSet.getLong(i++);
			String lang = resultSet.getString(i++);
			String coun = resultSet.getString(i++);
            
            /* Return the value object. */
            return new UsuarioProfileTO(login, enPassword, nombre, apellidos, email, idDir, numTarjeta.longValue(), lang, coun);
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }    
        
    }
        
   public void update(Connection connection, UsuarioProfileTO usuarioProfileTO) 
        throws InstanceNotFoundException, InternalErrorException {
        
        PreparedStatement preparedStatement = null;

        try {

            /* Create "preparedStatement". */
            String queryString = "UPDATE USUARIOPROFILE" +
                " SET ENPASSWORD = ?, NOMBRE = ?, APELLIDOS = ?," +
                " EMAIL = ?, IDDIR = ?, NUMTARJETA = ? , LANGUAGE = ?, COUNTRY = ? WHERE LOGIN = ?";
            preparedStatement = connection.prepareStatement(queryString);
            
            /* Fill "preparedStatement". */
            int i = 1;
            preparedStatement.setString(i++, usuarioProfileTO.getEncryptedPassword());
            preparedStatement.setString(i++, usuarioProfileTO.getNombre());
            preparedStatement.setString(i++, usuarioProfileTO.getApellidos());
            preparedStatement.setString(i++, usuarioProfileTO.getEmail());
            preparedStatement.setInt(i++, usuarioProfileTO.getIdDir());
            preparedStatement.setLong(i++, usuarioProfileTO.getNumTarjeta());
            preparedStatement.setString(i++, usuarioProfileTO.getLanguage());
            preparedStatement.setString(i++, usuarioProfileTO.getCountry());
            preparedStatement.setString(i++, usuarioProfileTO.getLogin());
                        
            /* Execute query. */
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(
                    usuarioProfileTO.getLogin(), 
                    UsuarioProfileTO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Duplicate row for login name = '" + 
                    usuarioProfileTO.getLogin() + "' in table 'USUARIOPROFILE'");
            }        
            
        } catch (SQLException e) {
            throw new InternalErrorException(e);    
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }            
        
    }

}
